//
//  Database.m
//  e-nurse
//
//  Created by  on 12/12/12.
//  Copyright (c) 2012 __MyCompanyName__. All rights reserved.
//

#import "Database.h"
#import "FMDatabase.h"
#import "Record.h"
#import "Utils.h"
#import "DateUtils.h"
#import "User.h"

#define DATABASE_NAME "e-nurse.db"

#define SQL_CREATE_RECORD_TABLE "CREATE TABLE [record]([id] INTEGER PRIMARY KEY AUTOINCREMENT, [userid] INTEGER NOT NULL, [hp] REAL NOT NULL DEFAULT 0, "\
                                "[lp] REAL NOT NULL DEFAULT 0, [hr] INTEGER NOT NULL DEFAULT 0, [tm] INTEGER NOT NULL DEFAULT 0,"\
                                "[desc] TEXT, [date] TEXT NOT NULL)"

#define SQL_INSERT_RECORD "INSERT INTO record (userid, hp, lp, hr, tm, desc, date) VALUES (?, ?, ?, ?, ?, ?, ?)"

#define SQL_SELECT_RECORD_BY_DATE "SELECT * FROM record WHERE date>=? AND date<=? AND userid=?"

#define SQL_SELECT_COUNT_OF_EXCEED_STD "SELECT COUNT(*) FROM record WHERE hp>140 OR lp>90"

#define SQL_CREATE_USER_TABLE "CREATE TABLE [user]([id] INTEGER PRIMARY KEY, [name] TEXT NOT NULL)"

#define SQL_INSERT_USER "INSERT INTO user (id, name) VALUES (?, ?)"

#define SQL_UPDATE_USER "UPDATE user SET name=? WHERE id=?"

#define SQL_SELECT_USER_BY_ID "SELECT * FROM user WHERE id=?"


static Database* sharedInstance = nil;

@interface Database()

@property (strong, nonatomic) FMDatabase* fmDatabase;

-(void)initializeDb;

@end

@implementation Database
@synthesize fmDatabase = _fmDatabase;

+(Database *)sharedInstance
{
    if (nil != sharedInstance) {
        return sharedInstance;
    }
    
    static dispatch_once_t pred;
    dispatch_once(&pred, ^{
        sharedInstance = [[Database alloc] init];
    });
    
    return sharedInstance;
}

- (id)init
{
    self = [super init];
    if(self)
    {
        NSString *documentsDirectoryPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
        NSString *dbPath = [[NSString alloc] initWithFormat:@"%@/%@", documentsDirectoryPath, @DATABASE_NAME];
        bool databaseInitialized = [[NSFileManager defaultManager] fileExistsAtPath:dbPath];
        self.fmDatabase = [FMDatabase databaseWithPath:dbPath];
        [self.fmDatabase open];
        if(!databaseInitialized)
        {
            [self initializeDb];
        }
        
        [dbPath release];
    }
    
    return self;
}

- (void)initializeDb
{
    if(![self.fmDatabase executeUpdate:@SQL_CREATE_RECORD_TABLE])
    {
        NSLog(@"Error: Created Record Table Failed!");
    }
    
    if(![self.fmDatabase executeUpdate:@SQL_CREATE_USER_TABLE])
    {
        NSLog(@"Error: Created User Table Failed!");
    }
}

- (void)dealloc
{
    [self.fmDatabase close];
    self.fmDatabase = nil;
    
    [super dealloc];
}

- (void)addUser:(User*)user
{
    NSMutableArray* args = [[NSMutableArray alloc] init];
    [args addObject:[NSNumber numberWithFloat:user.userId]];
    [args addObject:user.name];

    if(![self.fmDatabase executeUpdate:@SQL_INSERT_USER withArgumentsInArray:args])
    {
        NSLog(@"Error: Failed to add a user");
    }
    
    [args release];    
}

- (void)updateUser:(User *)user
{
    NSMutableArray* args = [[NSMutableArray alloc] init];
    [args addObject:user.name];
    [args addObject:[NSNumber numberWithFloat:user.userId]];
    
    if(![self.fmDatabase executeUpdate:@SQL_UPDATE_USER withArgumentsInArray:args])
    {
        NSLog(@"Error: Failed to update a user");
    }
    
    [args release];      
}

- (User*)getUserById:(int)userId
{
    User* result = nil;
    
    NSMutableArray* args = [[NSMutableArray alloc] init];
    [args addObject:[NSNumber numberWithInt:userId]];
    FMResultSet* s = [self.fmDatabase executeQuery:@SQL_SELECT_USER_BY_ID withArgumentsInArray:args];
    while ([s next]) 
    {
        result = [[User alloc] init];
        
        result.userId = [s intForColumn:@"id"];
        result.name = [s stringForColumn:@"name"];

    }
    [args release];  
    
    return [result autorelease];
}

- (void)addRecord:(Record *)record userId:(int)userId
{
    NSString* dateStr = [DateUtils formatDate:record.date];
    NSMutableArray* args = [[NSMutableArray alloc] init];
    [args addObject:[NSNumber numberWithFloat:userId]];
    [args addObject:[NSNumber numberWithFloat:record.highPressure]];
    [args addObject:[NSNumber numberWithFloat:record.lowPressure]];
    [args addObject:[NSNumber numberWithInt:record.heartRate]];
    [args addObject:[NSNumber numberWithInt:record.takenMedicine ? 1 : 0]];
    [args addObject:record.description];
    [args addObject:dateStr];
    if(![self.fmDatabase executeUpdate:@SQL_INSERT_RECORD withArgumentsInArray:args])
    {
        NSLog(@"Error: Failed to add a record");
    }
    
    [args release];
}

-(NSArray*)getRecordsByDate:(NSDate *)startDay endDay:(NSDate *)endDay userId:(int)userId
{
    NSMutableArray* result = [[[NSMutableArray alloc] init] autorelease];
    
    NSMutableArray* args = [[NSMutableArray alloc] init];
    [args addObject:[DateUtils formatDate:startDay]];
    [args addObject:[DateUtils formatDate:endDay]];
    [args addObject:[NSNumber numberWithInt:userId]];
    FMResultSet* s = [self.fmDatabase executeQuery:@SQL_SELECT_RECORD_BY_DATE withArgumentsInArray:args];
    while ([s next]) 
    {
        Record* record = [[Record alloc] init];
        [result addObject:record];
        
        record.recordId = [s intForColumn:@"id"];
        record.highPressure = [s doubleForColumn:@"hp"];
        record.lowPressure = [s doubleForColumn:@"lp"];
        record.heartRate = [s intForColumn:@"hr"];
        record.takenMedicine = [s boolForColumn:@"tm"];
        record.description = [s stringForColumn:@"desc"];
        record.date = [DateUtils dateFromString:[s stringForColumn:@"date"]];
        
        [record release];
    }
    [args release];
    
    return result;
}

- (NSUInteger)getExceedStdTimesOfCurrentWeek
{
    FMResultSet* s = [self.fmDatabase executeQuery:@SQL_SELECT_COUNT_OF_EXCEED_STD];
    while ([s next]) {
        return [s intForColumnIndex:0];
    }
    return 0;
}

@end
